﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using Catering_DBOpertion;

namespace Catering_Operation
{
    public class IntroduceNutrition
    {
        public DataTable GetAll()
        {
            string sql = @"SELECT *  FROM [CMS_Catering].[dbo].[View_IntroduceNutrition]";
            DataTable dt = DbHelperSQL.Query(sql).Tables[0];
            return dt;
        }
        public bool AddNutritionType(string Content)
        {
            bool flag = false;
            SqlParameter[] parm = new SqlParameter[] { new SqlParameter("@nutrition", SqlDbType.NVarChar, 200) };
            parm[0].Value = Content;
            string sql = @"INSERT INTO [CMS_Catering].[dbo].[NutritionType]
           ([nutrition]) VALUES (@nutrition)";
            int num = DbHelperSQL.ExecuteSql(sql, parm);
            if (num > 0)
            {
                flag = true;
            }
            return flag;
        }
        public int DelNutritionType(string id)
        {
            int num = 0;
            string sql = "delete From [NutritionType] where Id = @id";
            SqlParameter[] parm = new SqlParameter[] { 
                new SqlParameter("@id",id)
            };
            num = DbHelperSQL.ExecuteSql(sql, parm);
            return num;
        }

        public bool ISExistNutritionType(string Content)
        {
            SqlParameter[] parm = new SqlParameter[] { new SqlParameter("@nutrition", SqlDbType.NVarChar, 200) };
            parm[0].Value = Content;
            string sql = @"SELECT COUNT(*) FROM [NutritionType] WHERE nutrition=@nutrition ";
            return DbHelperSQL.Exists(sql, parm);
        }

        public DataTable GetINByID(int id)
        {
            string sql = @"SELECT *  FROM [CMS_Catering].[dbo].[View_IntroduceNutrition] where id =@id";
            SqlParameter[] parm = new SqlParameter[] { new SqlParameter("@id", id) };
            DataTable dt = DbHelperSQL.Query(sql, parm).Tables[0];
            return dt;
        }

        public int DeleteAllINByTypeId(string TypeId)
        {
            string selectsql = "select id from IntroduceNutrition where typeId = " + TypeId;
            DataTable dt = DbHelperSQL.Query(selectsql).Tables[0];
            int num = 0;
            if (dt != null && dt.Rows.Count > 0 && dt.Rows[0]["id"] != null)
            {
                string sql = "delete From [IntroduceNutrition] where typeId = @id";
                SqlParameter[] parm = new SqlParameter[] { 
                new SqlParameter("@id",TypeId)
            };
                num = DbHelperSQL.ExecuteSql(sql, parm);
            }
            else
            {
                num = 1;
            }
            return num;
        }

        public int AddIN(Catering_Model.IntroduceNutritionDBInfo INInfo)
        {
            try
            {
                int num = 0;
                string sql = @"INSERT INTO [CMS_Catering].[dbo].[IntroduceNutrition]
           ([typeId]
           ,[title]
           ,[cText]
           ,[userId])
     VALUES
           (@typeid,@title,@ctext,@userid)";
                SqlParameter[] parm = new SqlParameter[] { 
                new SqlParameter("@typeid",INInfo.Typeid),
                new SqlParameter("@title",INInfo.Title),
                new SqlParameter("@ctext",INInfo.CText),
                new SqlParameter("@userid",INInfo.UserId)
            };
                num = DbHelperSQL.ExecuteSql(sql, parm);
                return num;
            }
            catch
            {
                return 0;
            }
        }

        public int DelIN(int id)
        {
            int num = 0;
            string sql = "delete From [IntroduceNutrition] where id = @id";
            SqlParameter[] parm = new SqlParameter[] { 
                new SqlParameter("@id",id)
            };
            num = DbHelperSQL.ExecuteSql(sql, parm);
            return num;
        }

        public int UpdateIN(string title, string cText, int id, int typeid)
        {
            int num = 0;
            string sql = "update [IntroduceNutrition] set title =@title,cText=@c,typeId =@typeId  where id = @id";
            SqlParameter[] parm = new SqlParameter[] { 
                new SqlParameter("@title",title),
                new SqlParameter("@c",cText),
                new SqlParameter("@id",id),
                 new SqlParameter("@typeId",typeid)
            };
            num = DbHelperSQL.ExecuteSql(sql, parm);
            return num;
        }
    }
}
